package com.py.credit.utils;

import com.alibaba.fastjson.JSONArray;
import com.healthmarketscience.sqlbuilder.BinaryCondition;
import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.healthmarketscience.sqlbuilder.SetOperationQuery.Type;
import com.healthmarketscience.sqlbuilder.UnionQuery;
import com.py.credit.entity.Column;
import com.py.credit.entity.TableResult;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.util.*;

;

public class SQLBuilder {

    public static String createTableSql(String kId, String tableName, List<Column> columns) {

        StringBuffer sb = new StringBuffer();

        sb.append("CREATE TABLE ").append(tableName)
                .append("(record_id int(11) NOT NULL auto_increment COMMENT '记录编号 ',");

        for (Column column : columns) {

            String fieldDesc = column.getFieldDesc() == null ? "" : column.getFieldDesc();

            String fieldType = column.getFieldType();

            String metricCode = column.getMetricCode();

            if ("timestamp".equals(fieldType)) {

                sb.append(metricCode + " datetime comment '" + fieldDesc + "',");

            } else if ("number".equals(fieldType)) {

                int length = column.getFieldLength() + 6;

                sb.append(metricCode + " decimal(" + length + ", 6) comment '" + fieldDesc + "',");

            } else if ("varchar".equals(fieldType)) {

                if (column.getFieldLength() > 2000) {

                    sb.append(metricCode + " text comment '" + fieldDesc + "',");

                } else {

                    sb.append(metricCode + " varchar(" + column.getFieldLength() + ") comment '" + fieldDesc + "',");

                }

            }

        }

        sb.append("status varchar(1) COMMENT '状态,S:成功,A:已关联,T:无法关联,R:重复数据,P:错误数据修成功,E:错误数据,D:已删除,F:信用修复完成',");

        sb.append("tybm varchar(36) COMMENT '统一编码',");

        sb.append("isShow varchar(1) COMMENT '默认展示状态,Y:展示,A:已关联',");

        sb.append("md5 varchar(32) COMMENT 'MD5',");

        sb.append("batch_Id varchar(32) COMMENT '批次',");

        sb.append("address text COMMENT '注册地址',");

        sb.append("errorMess text COMMENT '错误信息',");

        sb.append("creditTime  datetime  NULL DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',");
        
        sb.append("updateTime  datetime  NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',");

        sb.append("PRIMARY KEY(record_id),");

        for (Column column : columns) {

            String isIndex = column.getIsIndex();
            String fieldType = column.getFieldType();
            Integer fieldLength = column.getFieldLength();

            if ("Y".equals(isIndex)) {

                if ("varchar".equals(fieldType)) {

                    if (fieldLength > 100) {

                        sb.append("KEY(").append(column.getMetricCode()).append("(").append(100).append(")")
                                .append("),");
                    } else {
                        sb.append("KEY(").append(column.getMetricCode()).append("(").append(fieldLength).append(")")
                                .append("),");
                    }

                } else {

                    sb.append("KEY(").append(column.getMetricCode()).append("),");
                }

            }

        }

        sb.append("KEY(tybm),");
        sb.append("KEY(status),");
        sb.append("KEY(batch_Id),");

        if ("100000".equals(kId)) {

            sb.append("KEY(A000001(100)),");

        } else if ("100001".equals(kId)) {

            sb.append("KEY(B000002),");

        } else if ("100002".equals(kId)) {

            sb.append("KEY(C000001(100)),");

        }

        sb.append("KEY(md5)");

        sb.append(")ENGINE=InnoDB DEFAULT CHARSET=utf8");

        return sb.toString();
    }

    /**
     * @function 创建内存表
     */
    public static String createTempTableSql(String tableName, List<Column> columns) {

        StringBuffer sb = new StringBuffer();

        sb.append("CREATE TABLE ").append(tableName).append("(");

        if (!CollectionUtils.isEmpty(columns)) {

            for (Column column : columns) {

                String fieldDesc = column.getFieldDesc() == null ? "" : column.getFieldDesc();

                String fieldType = column.getFieldType();

                String metricCode = column.getMetricCode();

                if ("timestamp".equals(fieldType)) {

                    sb.append(metricCode + " datetime comment '" + fieldDesc + "',");

                } else if ("number".equals(fieldType)) {

                    int length = column.getFieldLength() + 6;

                    sb.append(metricCode + " decimal(" + length + ", 6) comment '" + fieldDesc + "',");

                } else if ("varchar".equals(fieldType)) {

                    sb.append(metricCode + " varchar(" + column.getFieldLength() + ") comment '" + fieldDesc + "',");

                }

            }
        }

        sb.append("status varchar(1) COMMENT '状态,S:成功,A:已关联,T:无法关联,R:重复数据,P:错误数据修成功,E:错误数据,D:已删除,F:信用修复完成',");

        sb.append("tybm varchar(32) COMMENT '统一编码',");

        sb.append("isShow varchar(1) COMMENT '默认展示状态,Y:展示,A:已关联',");

        sb.append("md5 varchar(32) COMMENT 'MD5',");

        sb.append("batch_Id varchar(32) COMMENT '批次',");

        sb.append("address varchar(3000) COMMENT '注册地址',");

        sb.append("KEY(tybm),");

        sb.append("KEY(md5)");

        sb.append(")ENGINE=InnoDB DEFAULT CHARSET=utf8");

        return sb.toString();
    }

    public static String addColumnSql(String tableName, Column column) {

        StringBuffer sb = new StringBuffer();
        String isRequire = column.getIsRequire();
        String metricCode = column.getMetricCode();
        Integer fieldLength = column.getFieldLength();
        String fieldType = column.getFieldType();
        String fieldDesc = column.getFieldDesc() == null ? "" : column.getFieldDesc();

        sb.append("ALTER TABLE ").append(tableName).append(" add column ").append(metricCode);

        if ("timestamp".equals(fieldType)) {

            if ("Y".equals(isRequire)) {

                sb.append(" ").append("datetime not null").append(" COMMENT '").append(fieldDesc).append("'");
            } else {

                sb.append(" ").append("datetime").append(" COMMENT '").append(fieldDesc).append("'");

            }

        } else if ("number".equals(fieldType)) {

            if ("Y".equals(isRequire)) {

                sb.append(" ").append("decimal (").append(fieldLength + 6).append(",6) not null").append(" COMMENT '")
                        .append(fieldDesc).append("'");
            } else {

                sb.append(" ").append("decimal (").append(fieldLength + 6).append(",6)").append(" COMMENT '")
                        .append(fieldDesc).append("'");

            }

        } else if ("varchar".equals(fieldType)) {

            if (fieldLength > 2000) {

                if ("Y".equals(isRequire)) {

                    sb.append(" ").append("text not null").append(" COMMENT '").append(fieldDesc).append("'");
                } else {

                    sb.append(" ").append("text").append(" COMMENT '").append(fieldDesc).append("'");

                }

            } else {

                if ("Y".equals(isRequire)) {

                    sb.append(" ").append("varchar (").append(fieldLength).append(") not null").append(" COMMENT '")
                            .append(fieldDesc).append("'");
                } else {

                    sb.append(" ").append("varchar (").append(fieldLength).append(")").append(" COMMENT '")
                            .append(fieldDesc).append("'");

                }

            }
        }

        return sb.toString();

    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public static List<String> createConditionSql(String conditionStr) {

        List<String> conditionSql = new ArrayList<String>();

        if (StringUtils.isNotBlank(conditionStr)) {

            List<Map> condition = JSONArray.parseArray(conditionStr, Map.class);

            for (Map<String, Object> column : condition) {

                String type = column.get("type").toString();

                if ((boolean) column.get("fuzzy") && "varchar".equals(type)) {

                    conditionSql.add(BinaryCondition
                            .like(new CustomSql(column.get("colNameEN")), "%" + column.get("value").toString().trim() + "%")
                            .toString());

                } else if ("timestamp".equals(type) || "number".equals(type)) {

                    List<String> values = new ArrayList<>();

                    String value = "";

                    try {

                        values = (List<String>) column.get("value");

                    } catch (Exception e) {

                        value = column.get("value").toString().trim();

                    }

                    values.add(value);
                    values.add(value);

                    if (StringUtils.isNotBlank(values.get(0))) {

                        conditionSql.add(BinaryCondition
                                .greaterThan(new CustomSql(column.get("colNameEN")), values.get(0), true).toString());
                    }

                    if (StringUtils.isNotBlank(values.get(1))) {

                        conditionSql.add(BinaryCondition
                                .lessThan(new CustomSql(column.get("colNameEN")), values.get(1), true).toString());

                    }

                } else {

                    conditionSql.add(BinaryCondition
                            .equalTo(new CustomSql(column.get("colNameEN")), column.get("value").toString().trim()).toString());

                }

            }
        }
        return conditionSql;
    }

    public static String modifyColumnSql(String tableName, Column column) {

        StringBuilder sb = new StringBuilder();

        String fieldType = column.getFieldType();

        sb.append("ALTER TABLE ").append(tableName).append(" MODIFY ").append(column.getMetricCode()).append(" ");
        if("number".equals(fieldType)) {
        	sb.append("decimal");        	
        }else if ("timestamp".equals(fieldType)) {

            sb.append(" datetime ");

        }else {
        	sb.append(fieldType);        
        }

        if ("varchar".equals(fieldType)) {

            sb.append(" (").append(column.getFieldLength()).append(")");

        } else if ("number".equals(fieldType)) {

            sb.append(" (").append(column.getFieldLength()+6).append(",6)");
        }

        if ("Y".equals(column.getIsRequire())) {

            sb.append(" NOT NULL ");
        }

        if (StringUtils.isNotBlank(column.getDefaultValue())) {

            sb.append(" DEFAULT ").append("\"").append(column.getDefaultValue()).append("\"");
        }
        if(StringUtils.isNotBlank(column.getFieldDesc())) {
        	sb.append(" COMMENT ").append("\"").append(column.getFieldDesc()).append("\"");
        }
        return sb.toString();

    }

    private static String createSelectQuery(Integer rootId, List<TableResult> tableColumn) {
        Map<String, String> tableMap = new HashMap<>(tableColumn.size());
        SelectQuery selectQuery = new SelectQuery();
        //循环提取所有的字段存入allColumnsSql
        for (int i = 0; i < tableColumn.size(); i++) {
            String aliases = "a" + i;
            String tableNameEN = tableColumn.get(i).getTableNameEN();
            String tableName = "t_" + tableNameEN + " " + aliases;
            tableMap.put(aliases, tableName);
            List<Column> columns =  tableColumn.get(i).getColumns();
            //循环添加所有查询的值
            for (Column column : columns) {
                selectQuery.addCustomColumns(new CustomSql(aliases + "." + column.getMetricCode() +
                        " as " + tableNameEN + "_" + column.getMetricCode()));
                column.setAliases(tableNameEN + "_" + column.getMetricCode());
            }
        }
        //循环添加join
        for (String key : tableMap.keySet()) {
            selectQuery.addCustomJoin(SelectQuery.JoinType.INNER, "t_zoo_subject_unique_" + rootId + " a",
                    tableMap.get(key),
                    BinaryCondition.equalTo(new CustomSql("a.TYBM"), new CustomSql(key + ".TYBM")));
        }

        return selectQuery.validate().toString();

    }

    public static String createViewQuery(String viewName, Integer rootId, List<TableResult> tableColumn) {

        StringBuilder viewBuilder = new StringBuilder();

        String selectQuery = createSelectQuery(rootId, tableColumn);

        viewBuilder.append("CREATE VIEW ").append(viewName).append(" AS ").append(selectQuery);

        return viewBuilder.toString();

    }

}
